
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 04/29/2011 00:19:50
-- Generated from EDMX file: D:\Libary\Role Based Access Control\RBAC\RBAC.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [rbac];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_ACTOR_REFERENCE_ACCOUNT]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Actor] DROP CONSTRAINT [FK_ACTOR_REFERENCE_ACCOUNT];
GO
IF OBJECT_ID(N'[dbo].[FK_ACTOR_REFERENCE_ROLE]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Actor] DROP CONSTRAINT [FK_ACTOR_REFERENCE_ROLE];
GO
IF OBJECT_ID(N'[dbo].[FK_PRIVILEG_REFERENCE_RESOURCE]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Privilege] DROP CONSTRAINT [FK_PRIVILEG_REFERENCE_RESOURCE];
GO
IF OBJECT_ID(N'[dbo].[FK_PRIVILEG_REFERENCE_ROLE]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Privilege] DROP CONSTRAINT [FK_PRIVILEG_REFERENCE_ROLE];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Account]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Account];
GO
IF OBJECT_ID(N'[dbo].[Actor]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Actor];
GO
IF OBJECT_ID(N'[dbo].[Privilege]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Privilege];
GO
IF OBJECT_ID(N'[dbo].[Resource]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Resource];
GO
IF OBJECT_ID(N'[dbo].[Role]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Role];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Accounts'
CREATE TABLE [dbo].[Accounts] (
    [ID] nvarchar(64)  NOT NULL,
    [Password] varchar(128)  NOT NULL,
    [Display] nvarchar(1024)  NULL
);
GO

-- Creating table 'Actors'
CREATE TABLE [dbo].[Actors] (
    [ID] int  NOT NULL,
    [Account_ID] nvarchar(64)  NULL,
    [Role_ID] nvarchar(64)  NULL
);
GO

-- Creating table 'Privileges'
CREATE TABLE [dbo].[Privileges] (
    [ID] int  NOT NULL,
    [Resource_ID] varchar(800)  NULL,
    [Role_ID] nvarchar(64)  NULL
);
GO

-- Creating table 'Resources'
CREATE TABLE [dbo].[Resources] (
    [ID] varchar(800)  NOT NULL,
    [Display] nvarchar(1024)  NOT NULL
);
GO

-- Creating table 'Roles'
CREATE TABLE [dbo].[Roles] (
    [ID] nvarchar(64)  NOT NULL,
    [Display] nvarchar(1024)  NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ID] in table 'Accounts'
ALTER TABLE [dbo].[Accounts]
ADD CONSTRAINT [PK_Accounts]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Actors'
ALTER TABLE [dbo].[Actors]
ADD CONSTRAINT [PK_Actors]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Privileges'
ALTER TABLE [dbo].[Privileges]
ADD CONSTRAINT [PK_Privileges]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Resources'
ALTER TABLE [dbo].[Resources]
ADD CONSTRAINT [PK_Resources]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Roles'
ALTER TABLE [dbo].[Roles]
ADD CONSTRAINT [PK_Roles]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Account_ID] in table 'Actors'
ALTER TABLE [dbo].[Actors]
ADD CONSTRAINT [FK_ACTOR_REFERENCE_ACCOUNT]
    FOREIGN KEY ([Account_ID])
    REFERENCES [dbo].[Accounts]
        ([ID])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ACTOR_REFERENCE_ACCOUNT'
CREATE INDEX [IX_FK_ACTOR_REFERENCE_ACCOUNT]
ON [dbo].[Actors]
    ([Account_ID]);
GO

-- Creating foreign key on [Role_ID] in table 'Actors'
ALTER TABLE [dbo].[Actors]
ADD CONSTRAINT [FK_ACTOR_REFERENCE_ROLE]
    FOREIGN KEY ([Role_ID])
    REFERENCES [dbo].[Roles]
        ([ID])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ACTOR_REFERENCE_ROLE'
CREATE INDEX [IX_FK_ACTOR_REFERENCE_ROLE]
ON [dbo].[Actors]
    ([Role_ID]);
GO

-- Creating foreign key on [Resource_ID] in table 'Privileges'
ALTER TABLE [dbo].[Privileges]
ADD CONSTRAINT [FK_PRIVILEG_REFERENCE_RESOURCE]
    FOREIGN KEY ([Resource_ID])
    REFERENCES [dbo].[Resources]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PRIVILEG_REFERENCE_RESOURCE'
CREATE INDEX [IX_FK_PRIVILEG_REFERENCE_RESOURCE]
ON [dbo].[Privileges]
    ([Resource_ID]);
GO

-- Creating foreign key on [Role_ID] in table 'Privileges'
ALTER TABLE [dbo].[Privileges]
ADD CONSTRAINT [FK_PRIVILEG_REFERENCE_ROLE]
    FOREIGN KEY ([Role_ID])
    REFERENCES [dbo].[Roles]
        ([ID])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PRIVILEG_REFERENCE_ROLE'
CREATE INDEX [IX_FK_PRIVILEG_REFERENCE_ROLE]
ON [dbo].[Privileges]
    ([Role_ID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------